package com.yuntu.dao.impl;

import com.yuntu.dao.SubjectDao;
import com.yuntu.entity.Subject;
import com.yuntu.util.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author MrWang-shuizhishuiheshui
 * @description dao层的 实现类
 */
public class SubjectDaoImpl implements SubjectDao {

    @Override
    public boolean insert(Subject subject) {
        Connection con=null;
        PreparedStatement ps =null;
        boolean flag=false;
        try {
            //1.加载驱动
            //2.建立连接
            con= JDBCUtils.getConnect();
            //3.创建执行对象
            // 创建PrepareStatement对象的同时,
            // 直接将sql语句传入,进行优化预编译
            // ?代表占位符,进行参数传递时,所占的位置
            String sql="insert into subject(subjectname,classhour,gradeid) values(?,?,?)";
            ps = con.prepareStatement(sql);
            //填充占位符
            ps.setString(1,subject.getSubjectName());
            ps.setInt(2,subject.getClassHour());
            ps.setInt(3,subject.getGradeId());
            //执行
            int i = ps.executeUpdate();
            //4.处理结果
            if (i>0){
                flag= true;
            }else{
                flag= false;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //5.释放资源
            JDBCUtils.closeAll(con,ps,null);
        }
        return flag;
    }

    @Override
    public boolean delete(int subjectNo) {
        //创建返回值类型数据
        boolean flag=false;
        //创建连接对象和预编译对象
        Connection con=null;
        PreparedStatement ps=null;
        //创建sql语句
        String sql="delete from subject where subjectno=?";
        try {
            con=JDBCUtils.getConnect();
            ps=con.prepareStatement(sql);
            ps.setInt(1,subjectNo);
            int i = ps.executeUpdate();
            if (i>0){
                flag=true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeAll(con,ps,null);
        }
        return flag;
    }

    @Override
    public boolean update(Subject subject) {
        boolean flag=false;
        //创建连接对象和预编译对象
        Connection con=null;
        PreparedStatement ps=null;
        //创建sql语句
        String sql="update subject set subjectname=? where subjectno=?";

        try {
            con=JDBCUtils.getConnect();
            ps=con.prepareStatement(sql);
            ps.setString(1,subject.getSubjectName());
            ps.setInt(2,subject.getSubjectNo());
            int i = ps.executeUpdate();
            if(i>0){
                flag=true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeAll(con,ps,null);
        }
        return flag;
    }

    @Override
    public Subject selectOneByNo(int subjectNo) {
        //创建用于返回的对象
        Subject subject=new Subject();
        //创建连接对象和预编译对象和结果集对象
        Connection con=null;
        PreparedStatement ps=null;
        ResultSet rs = null;
        //创建sql语句
        String sql="select * from subject where subjectno=?";

        try {
            con=JDBCUtils.getConnect();
            ps=con.prepareStatement(sql);
            ps.setInt(1,subjectNo);
            //执行查询操作
            rs = ps.executeQuery();
            if (rs.next()){
                subject.setSubjectNo(rs.getInt("subjectno"));
                subject.setSubjectName(rs.getString("subjectname"));
                subject.setClassHour(rs.getInt("classhour"));
                subject.setGradeId(rs.getInt("gradeid"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeAll(con,ps,rs);
        }
        return subject;
    }

    @Override
    public List<Subject> selectAll() {
        List<Subject> list=new ArrayList<>();
        //创建连接对象和预编译对象和结果集对象
        Connection con=null;
        PreparedStatement ps=null;
        ResultSet rs = null;
        //创建sql语句
        String sql="select * from subject";

        try {
            con=JDBCUtils.getConnect();
            ps=con.prepareStatement(sql);
            //执行查询操作
            rs = ps.executeQuery();
            while(rs.next()){
                Subject subject=new Subject();
                subject.setSubjectNo(rs.getInt("subjectno"));
                subject.setSubjectName(rs.getString("subjectname"));
                subject.setClassHour(rs.getInt("classhour"));
                subject.setGradeId(rs.getInt("gradeid"));
                //将当前对象添加到集合中
                list.add(subject);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeAll(con,ps,rs);
        }
        return list;
    }
}
